package cn.fastoo.common.sampleframe.utils;

import cn.fastoo.common.sampleframe.SqlUtils;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVPrinter;
import org.apache.poi.hssf.usermodel.*;

import javax.servlet.ServletOutputStream;
import java.beans.PropertyDescriptor;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.List;
import java.util.Map;
import java.util.Set;


public class ExcelUtil {

	public static <T>  void  export(Class<?> clazz,List<T> list, String[] titles,String[] fields,ServletOutputStream  outputStream){
	        // 创建一个workbook 对应一个excel应用文件  
			HSSFWorkbook workBook = new HSSFWorkbook();  
	        // 在workbook中添加一个sheet,对应Excel文件中的sheet  
			HSSFSheet sheet = workBook.createSheet("导出excel例子");  
			HSSFExportUtil exportUtil = new HSSFExportUtil(workBook, sheet);  
	        HSSFCellStyle headStyle = exportUtil.getHeadStyle();  
	        HSSFCellStyle bodyStyle = exportUtil.getBodyStyle();  
	        // 构建表头  
	        HSSFRow headRow = sheet.createRow(0);  
	        HSSFCell cell = null;  
	        for (int i = 0; i < titles.length; i++)  
	        {  
	            cell = headRow.createCell(i);  
	            cell.setCellStyle(headStyle);  
	            cell.setCellValue(titles[i]);  
	        }  
	        // 构建表体数据  
	        if (list != null && list.size() > 0)  
	        {  
	            for (int j = 0; j < list.size(); j++)  
	            {  
	                HSSFRow bodyRow = sheet.createRow(j + 1);  
	                Object obj = list.get(j);  
	                for(int i = 0;i<fields.length;i++){
	                	 cell = bodyRow.createCell(i);  
	 	                cell.setCellStyle(bodyStyle);  
	 	                try {
	 	                	SqlUtils.JPAInfo jpaInfo = SqlUtils.getJPAInfo(clazz);
	 	                	PropertyDescriptor readPD = jpaInfo.getFieldPDMap().get(fields[i]);
	 						Object value = readPD.getReadMethod().invoke(obj);
	 						cell.setCellValue(value==null?"":value.toString());  
	 					} catch (Exception e) {
	 						e.printStackTrace();
	 					}  
	                }
	            }  
	        }  
	        try  
	        {  
	            workBook.write(outputStream);  
	            outputStream.flush();  
	            outputStream.close();  
	        }  
	        catch (IOException e)  
	        {  
	            e.printStackTrace();  
	        }  
	        finally  
	        {  
	            try  
	            {  
	                outputStream.close();  
	            }  
	            catch (IOException e)  
	            {  
	                e.printStackTrace();  
	            }  
	        }  
	  
	}

	public static void export(List<Map<String, Object>> data,String path) {
		// 创建一个workbook 对应一个excel应用文件
		HSSFWorkbook workBook = new HSSFWorkbook();
		// 在workbook中添加一个sheet,对应Excel文件中的sheet
		HSSFSheet sheet = workBook.createSheet("导出excel例子");
		HSSFExportUtil exportUtil = new HSSFExportUtil(workBook, sheet);
		HSSFCellStyle headStyle = exportUtil.getHeadStyle();
		HSSFCellStyle bodyStyle = exportUtil.getBodyStyle();
		// 构建表头
		HSSFRow headRow = sheet.createRow(0);
		HSSFCell cell = null;
		int i =0 ;
		Set<String> keySet = data.get(0).keySet();
		for (String key : keySet ){
			cell = headRow.createCell(i);
			cell.setCellStyle(headStyle);
			cell.setCellValue(key);
			i++;
		}
		// 构建表体数据

		if (data != null && data.size() > 0)
		{
			for (int j = 0; j < data.size(); j++)
			{
				HSSFRow bodyRow = sheet.createRow(j + 1);
				Map<String,Object> obj = data.get(j);
				int h =0 ;
				for(String key: keySet){
					cell = bodyRow.createCell(h);
					cell.setCellStyle(bodyStyle);
					try {
						Object value =  obj.get(key);
						cell.setCellValue(value==null?"":value.toString());
					} catch (Exception e) {
						e.printStackTrace();
					}
					h++;
				}
			}
		}
		try {
			FileOutputStream fos = new FileOutputStream(path,true);
			workBook.write(fos);
			fos.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		//workBook
	}
	private static final String NEW_LINE_SEPARATOR = "\n";
	public static void exportCSV(ResultSet rs,String path) throws Exception {
		FileWriter fileWriter = null;
		CSVPrinter csvFilePrinter = null;
		//创建 CSVFormat
		CSVFormat csvFileFormat = CSVFormat.DEFAULT.withRecordSeparator(NEW_LINE_SEPARATOR);

		System.out.println("开始创建CSV文件~~~");
		//初始化FileWriter
		fileWriter = new FileWriter(path);
		//初始化 CSVPrinter
		csvFilePrinter = new CSVPrinter(fileWriter, csvFileFormat);
		//创建CSV文件头
		ResultSetMetaData rsmd = rs.getMetaData();
		String[] name=new String[rsmd.getColumnCount()];
		int columnCount = rs.getMetaData().getColumnCount();
		for(int i = 0;i<columnCount;i++){
			name[i] = rsmd.getColumnName(i+1);
		}
		csvFilePrinter.printRecord(name);
		csvFilePrinter.printRecords(rs);
		while(rs.next()) {
			for(int i = 1; i <= columnCount; ++i) {
				csvFilePrinter.print(rs.getObject(i));
			}
			csvFilePrinter.println();
		}
		System.out.println("CSV文件创建成功~~~");


		try {
			fileWriter.flush();
			fileWriter.close();
			csvFilePrinter.close();
		} catch (IOException e) {
			e.printStackTrace();
		}

	}


	public static void export(List<Map<String, Object>> data, ServletOutputStream outputStream) {
		// 创建一个workbook 对应一个excel应用文件
		HSSFWorkbook workBook = new HSSFWorkbook();
		// 在workbook中添加一个sheet,对应Excel文件中的sheet
		HSSFSheet sheet = workBook.createSheet("导出excel例子");
		HSSFExportUtil exportUtil = new HSSFExportUtil(workBook, sheet);
		HSSFCellStyle headStyle = exportUtil.getHeadStyle();
		HSSFCellStyle bodyStyle = exportUtil.getBodyStyle();
		// 构建表头
		HSSFRow headRow = sheet.createRow(0);
		HSSFCell cell = null;
		int i =0 ;
		Set<String> keySet = data.get(0).keySet();
		for (String key : keySet ){
			cell = headRow.createCell(i);
			cell.setCellStyle(headStyle);
			cell.setCellValue(key);
			i++;
		}
		// 构建表体数据

		if (data != null && data.size() > 0)
		{
			for (int j = 0; j < data.size(); j++)
			{
				HSSFRow bodyRow = sheet.createRow(j + 1);
				Map<String,Object> obj = data.get(j);
				int h =0 ;
				for(String key: keySet){
					cell = bodyRow.createCell(h);
					cell.setCellStyle(bodyStyle);
					try {
						Object value =  obj.get(key);
						cell.setCellValue(value==null?"":value.toString());
					} catch (Exception e) {
						e.printStackTrace();
					}
					h++;
				}
			}
		}
		try
		{
			workBook.write(outputStream);
			outputStream.flush();
			outputStream.close();
		}
		catch (IOException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				outputStream.close();
			}
			catch (IOException e)
			{
				e.printStackTrace();
			}
		}

	}
}
